<?php

$config_properties = require 'qrc_conf.properties.php';
$con = mysqli_connect($config_properties['domain'], $config_properties['username'], $config_properties['password']);

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

//Create database if not exists
$sql = "CREATE DATABASE IF NOT EXISTS " . $config_properties['databasename'];
if (mysqli_query($con, $sql)) {
    //Create all table when the program start    
    $con_create_tbl = mysqli_connect($config_properties['domain'], $config_properties['username'], $config_properties['password'], $config_properties['databasename']);
    mysqli_set_charset($con_create_tbl, "utf8");

    $sqlCreateCaLeftTbl = "CREATE TABLE IF NOT EXISTS SWISS_CA_MENU ("
            . "CA_ID VARCHAR(100),"
            . "CA_NAME VARCHAR(200),"
            . "CA_LINK VARCHAR(255),"
            . "CA_FILE_PATH VARCHAR(255),"
            . "CA_POSITION_TYPE VARCHAR(15),"
            . "PRIMARY KEY (CA_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateCaLeftTbl);

    $sqlCreateContactTbl = "CREATE TABLE IF NOT EXISTS SWISS_MANAGE_CONTACT ("
            . "CONTACT_ID VARCHAR(100),"
            . "CONTACT_DESC_DESC MEDIUMTEXT,"
            . "CREATED_DATE_TIME TIMESTAMP,"
            . "PRIMARY KEY (CONTACT_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateContactTbl);
    $sqlCreateDescTbl = "CREATE TABLE IF NOT EXISTS SWISS_PAGE_DESCRIPTION ("
            . "DESC_ID VARCHAR(100),"
            . "DESC_NAME VARCHAR(255),"
            . "DESC_DESC MEDIUMTEXT,"
            . "CREATED_DATE_TIME TIMESTAMP,"
            . "MENU_ID VARCHAR(200),"
            . "PAGE_TYPE VARCHAR(10),"
            . "PRIMARY KEY (DESC_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateDescTbl);

    $sqlCreateImageTbl = "CREATE TABLE IF NOT EXISTS SWISS_IMAGE_CONTENT ("
            . "IMAGE_ID VARCHAR(100),"
            . "IMAGE_NAME VARCHAR(200),"
            . "IMAGE_PATH VARCHAR(255),"
            . "DESC_ID VARCHAR(100),"
            . "CREATED_DATE_TIME TIMESTAMP,"
            . "PRIMARY KEY (IMAGE_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateImageTbl);
    $sqlCreateFILETbl = "CREATE TABLE IF NOT EXISTS SWISS_FILE_CONTENT ("
            . "FILE_ID VARCHAR(100),"
            . "FILE_NAME VARCHAR(200),"
            . "FILE_PATH VARCHAR(255),"
            . "DESC_ID VARCHAR(100),"
            . "CREATED_DATE_TIME TIMESTAMP,"
            . "PRIMARY KEY (FILE_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateFILETbl);

    $sqlCreateProject = "CREATE TABLE IF NOT EXISTS SWISS_MENU ("
            . "MENU_ID VARCHAR(100),"
            . "MENU_NAME VARCHAR(200),"
            . "CREATED_DATE_TIME VARCHAR(200),"
            . "PRIMARY KEY (MENU_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateProject);
    $sqlCreateProject1 = "CREATE TABLE IF NOT EXISTS SWISS_MENU_LEVEL1 ("
            . "MENU_ID VARCHAR(100),"
            . "MENU_NAME VARCHAR(200),"
            . "CREATED_DATE_TIME VARCHAR(200),"
            . "CONTENT_PAGE_ID VARCHAR(200),"
            . "REF_ID VARCHAR(200),"
            . "PRIMARY KEY (MENU_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateProject1);
    $sqlCreateProject2 = "CREATE TABLE IF NOT EXISTS SWISS_MENU_LEVEL2 ("
            . "MENU_ID VARCHAR(100),"
            . "MENU_NAME VARCHAR(200),"
            . "CREATED_DATE_TIME VARCHAR(200),"
            . "CONTENT_PAGE_ID VARCHAR(200),"
            . "REF_ID VARCHAR(200),"
            . "PRIMARY KEY (MENU_ID)"
            . ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateProject2);
    $sqlCreateTableUsers = "CREATE TABLE IF NOT EXISTS SWISS_USERS (
                            id varchar(10) NOT NULL,
                            username varchar(30),
                            password varchar(255),
                            fName varchar(50),
                            lName varchar(50),
                            email varchar(100) ,
                            gender varchar(10),
                            age varchar(10),
                            dob date,
                            permission_id varchar(10),
                            PRIMARY KEY  (id),
                            FOREIGN KEY (permission_id) REFERENCES QRC_USER_PERMISSION(P_Id)
                            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
    mysqli_query($con_create_tbl, $sqlCreateTableUsers);
} else {
    echo "Error creating database: " . mysqli_error($con);
}